package com.deyang.common.utils;

import org.apache.commons.codec.binary.Base64;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.RequestParam;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * 描述: POI工具类
 * 作者: lihy
 * 时间: 2018年5月23日
 * 版本: 1.0
 *
 */
public class POIUtil {

	private static final Log log = LogFactory.getLog(POIUtil.class);
	
	private static Workbook wb;
	private static Sheet sheet;
	private static Row row;

	/**
	 * excel导出
	 * 
	 * @param templatePath 模板路径
	 */
	@SuppressWarnings("unused")
	public static String export(String templatePath) {
		// 导出文件存放目录
		String filePath = DirUtil.getWebRootPath() + File.separator + "exportFile";
		File fileDir = new File(filePath);
		if (!fileDir.exists()) {
			fileDir.mkdir();
		}

		// 导出文件路径
		String path = filePath + File.separator + ".xlsx";

		XSSFWorkbook wb = null;
		SXSSFWorkbook swb = null;
		FileOutputStream os = null;
		try {
			// 1.载入模板
			wb = new XSSFWorkbook(new File(templatePath)); // 初始化HSSFWorkbook对象
			wb.setSheetName(0, "用户信息导出");
			Sheet sheet = wb.getSheetAt(0); // wb.createSheet("监控点资源状态");

			// 2.读取模板处理好样式

			// 3.转换成大数据读取模式
			swb = new SXSSFWorkbook(wb, 1000); // 用于大文件导出
			sheet = swb.getSheetAt(0);

			// 4.大批量写入数据

			// 5.保存到本地文件夹
			os = new FileOutputStream(new File(path));
			swb.write(os);

			return path;
		} catch (IOException e) {
			if(log.isErrorEnabled()) log.error("导出失败：" + e.getMessage());
			e.printStackTrace();
			return null;
		} catch (InvalidFormatException e) {
			if(log.isErrorEnabled()) log.error("导出失败：" + e.getMessage());
			e.printStackTrace();
			return null;
		} finally {
			close(os, swb, wb);
		}
	}

	/**
	 * 资源关闭
	 * 
	 * @param os
	 * @param wb
	 * @param swb
	 */
	public static void close(FileOutputStream os, SXSSFWorkbook swb, XSSFWorkbook wb) {
		if (null != os) {
			try {
				os.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

		if (null != swb) {
			try {
				swb.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

		if (null != wb) {
			try {
				wb.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 将文档写入文件
	 * 
	 * @param wb
	 * @param name
	 */
	public static String writeExcel(SXSSFWorkbook wb, String name) {
		String filePath = DirUtil.getWebRootPath() + File.separator + "WEB-INF" + File.separator + "files" + File.separator + "export";
		File f = new File(filePath);
		if (!f.exists()) {
			f.mkdirs();
		}
		
		String path = filePath + File.separator + name + ".xlsx";
		
		FileOutputStream os = null;
		try {
			File file = new File(path);
			os = new FileOutputStream(file);
			wb.write(os);
			os.close();
		} catch (FileNotFoundException e) {
			log.error("文件路径找不到.", e);
		} catch (IOException e) {
			log.error("文件IO异常.", e);
		} finally {
			if (null != os) {
				try {
					os.close();
				} catch (IOException e) {
					log.error("文件IO异常.", e);
				}
			}
		}
		
		return path;
	}

//	/**
//	 * 导出标题样式
//	 */
//	public static void setTitleFont(SXSSFWorkbook wb, Iterator<Cell> it) {
//		XSSFColor color = new XSSFColor(new java.awt.Color(219, 229, 241));
//		Font font = createFont(wb, Font.BOLDWEIGHT_BOLD, Font.COLOR_NORMAL, (short) 11);
//		CellStyle style = createCellStyle(wb, color, CellStyle.ALIGN_CENTER, font);
//		while (it.hasNext()) {
//			it.next().setCellStyle(style);
//		}
//	}

//	/**
//	 * 导出表头样式
//	 */
//	public static void setHeadStyle(SXSSFWorkbook wb, Iterator<Cell> it) {
//		XSSFColor color = new XSSFColor(new java.awt.Color(79, 129, 189));
//		Font font = createFont(wb, Font.BOLDWEIGHT_NORMAL, HSSFColor.WHITE.index, (short) 11);
//		CellStyle style = createCellStyle(wb, color, CellStyle.ALIGN_CENTER, font);
//		while (it.hasNext()) {
//			it.next().setCellStyle(style);
//		}
//	}

//	/**
//	 * 导出表数据样式 默认居中
//	 */
//	public static void setContentStyle(SXSSFWorkbook wb, Iterator<Cell> it) {
//		XSSFColor color = new XSSFColor(new java.awt.Color(255, 255, 255));
//		Font font = createFont(wb, Font.BOLDWEIGHT_NORMAL, Font.COLOR_NORMAL, (short) 10);
//		CellStyle style = createBorderCellStyle(wb, HSSFColor.WHITE.index, color, CellStyle.ALIGN_CENTER, font);
//		while (it.hasNext()) {
//			it.next().setCellStyle(style);
//		}
//	}

//	/**
//	 * 导出表数据样式 左对齐
//	 */
//	public static void setContentLeftStyle(SXSSFWorkbook wb, Cell cell) {
//		XSSFColor color = new XSSFColor(new java.awt.Color(255, 255, 255));
//		Font font = createFont(wb, Font.BOLDWEIGHT_NORMAL, Font.COLOR_NORMAL, (short) 10);
//		CellStyle style = createBorderCellStyle(wb, HSSFColor.WHITE.index, color, CellStyle.ALIGN_LEFT, font);
//		style.setWrapText(true); // 实现换行
//		cell.setCellStyle(style);
//	}

//	/**
//	 * 设置合并单元格边框
//	 */
//	public static void setBorderStyle(Workbook wb, Sheet sheet, CellRangeAddress cra) {
//		int border = HSSFColor.WHITE.index;
//		RegionUtil.setBorderBottom(border, cra, sheet, wb);
//		RegionUtil.setBorderLeft(border, cra, sheet, wb);
//		RegionUtil.setBorderRight(border, cra, sheet, wb);
//		RegionUtil.setBorderTop(border, cra, sheet, wb);
//	}

	/**
	 * 功能：创建HSSFSheet工作簿
	 *
	 * @param wb  SXSSFWorkbook
	 * @param sheetName String
	 * @return HSSFSheet
	 */
	public static Sheet createSheet(SXSSFWorkbook wb, String sheetName) {
		Sheet sheet = wb.createSheet(sheetName);
		sheet.setDefaultColumnWidth(30);
		sheet.setColumnWidth(0, 7 * 256);
		sheet.setDefaultRowHeight((short) 400);
		sheet.setDisplayGridlines(true);
		return sheet;
	}

	/**
	 * 功能：创建CellStyle样式
	 *
	 * @param wb SXSSFWorkbook
	 * @param color  背景色
	 * @param align 前置色
	 * @param font  字体
	 * @return CellStyle
	 */
//	public static CellStyle createCellStyle(SXSSFWorkbook wb, XSSFColor color, short align, Font font) {
//		XSSFCellStyle cs = (XSSFCellStyle) wb.createCellStyle();
//		cs.setAlignment(align); // 水平居中
//		cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//		cs.setFillForegroundColor(color);
//		cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
//		cs.setFont(font);
//		return cs;
//	}

//	/**
//	 * 功能：创建带边框的CellStyle样式
//	 *
//	 * @param wb SXSSFWorkbook
//	 * @param backgroundColor 背景色
//	 * @param foregroundColor 前置色
//	 * @param font 字体
//	 * @return CellStyle
//	 */
//	public static CellStyle createBorderCellStyle(SXSSFWorkbook wb, short backgroundColor, XSSFColor foregroundColor,
//                                                  short halign, Font font) {
//		XSSFCellStyle cs = (XSSFCellStyle) wb.createCellStyle();
//		cs.setAlignment(halign);
//		cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//		cs.setFillBackgroundColor(backgroundColor);
//		cs.setFillForegroundColor(foregroundColor);
//		cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
//		cs.setFont(font);
//		cs.setBorderLeft(CellStyle.BORDER_DASHED);
//		cs.setLeftBorderColor(HSSFColor.GREY_80_PERCENT.index);
//		cs.setBorderRight(CellStyle.BORDER_DASHED);
//		cs.setRightBorderColor(HSSFColor.GREY_80_PERCENT.index);
//		cs.setBorderTop(CellStyle.BORDER_DASHED);
//		cs.setTopBorderColor(HSSFColor.GREY_80_PERCENT.index);
//		cs.setBorderBottom(CellStyle.BORDER_DASHED);
//		cs.setBottomBorderColor(HSSFColor.GREY_80_PERCENT.index);
//		return cs;
//	}

	/**
	 * 功能：创建字体
	 *
	 * @param wb HSSFWorkbook
	 * @param boldweight short
	 * @param color short
	 * @return Font
	 */
	public static Font createFont(SXSSFWorkbook wb, short boldweight, short color, short size) {
		Font font = wb.createFont();
//		font.setBoldweight(boldweight);
		font.setColor(color);
		font.setFontHeightInPoints(size);
		return font;
	}

	/**
	 * 功能：合并单元格
	 *
	 * @param sheet  Sheet
	 * @param firstRow  int
	 * @param lastRow  int
	 * @param firstColumn int
	 * @param lastColumn int
	 * @return int 合并区域号码
	 */
	public static int mergeCell(Sheet sheet, int firstRow, int lastRow, int firstColumn, int lastColumn) {
		return sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn));
	}

	/**
	 * 功能：创建Row
	 *
	 * @param sheet Sheet
	 * @param rowNum int
	 * @param height int
	 * @return HSSFRow
	 */
	public static Row createRow(Sheet sheet, int rowNum, int height) {
		Row row = sheet.createRow(rowNum);
		row.setHeight((short) height);
		return row;
	}

//	public static String nullVal(Object val){
//		if(val == null){
//			return "";
//		}
//		return val;
//	}
	
	/**
	 * 读取Excel数据内容
	 *
	 * @return Map 包含单元格数据内容的Map对象
	 */
	public static List<Map<String, Object>> readExcelContent(InputStream is) {
		List<Map<String, Object>> list = null;
		try {
			//wb = new XSSFWorkbook(is);
			 wb = WorkbookFactory.create(is);
			sheet = wb.getSheetAt(0);
			// 得到总行数
			int rowNum = sheet.getLastRowNum();
			// 总列数
			row = sheet.getRow(0);
			int colNum = row.getLastCellNum();
			// 避免list多次扩容
			// list = new LinkedList<Map<String,Object>>();
			list = new ArrayList<Map<String, Object>>((int) (rowNum / 0.75));
			// 正文内容应该从第二行开始,第一行为表头的标题
			for (int i = 1; i <= rowNum; i++) {
				// 整行为空，continue
				row = sheet.getRow(i);
				if (row == null)
				{continue;}
				// 避免HashMap多次扩容
				Map<String, Object> rowM = new HashMap<String, Object>();
				for (int j = 0; j < colNum; j++) {
                    rowM.put("field" + j, getCellFormatValue(row.getCell(j))
							.trim());
				}
				list.add(rowM);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("导入Excel文件失败");
		}
		return list;
	}

	
	/**
	 * 读取Excel数据内容
	 * 标题匹配
	 * @param
	 * @return Map 包含单元格数据内容的Map对象
	 */
	public static List<Map<String, Object>> readExcelContent1(InputStream is) {
		List<Map<String, Object>> list = null;
		try {
			Workbook wb = WorkbookFactory.create(is);
			//wb = new XSSFWorkbook(is);

			sheet = wb.getSheetAt(0);
			// 得到总行数
			int rowNum = sheet.getLastRowNum();
			// 总列数
			row = sheet.getRow(0);
			for (Row row : sheet) {
				int index = 0;
				for (Cell cell : row) {
					//读取数据前设置单元格类型
//					cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					String value = cell.getStringCellValue();
					System.out.print("value:" + value + " ");
					index++;
				}
				System.out.println();
			}


			Row titlList = sheet.getRow(0);
			int colNum = row.getLastCellNum();
			// 避免list多次扩容
			// list = new LinkedList<Map<String,Object>>();
			list = new ArrayList<Map<String, Object>>((int) (rowNum / 0.75));
			// 正文内容应该从第二行开始,第一行为表头的标题
			for (int i = 1; i <= rowNum; i++) {
				// 整行为空，continue
				row = sheet.getRow(i);
				if (row == null)
					continue;
				// 避免HashMap多次扩容
				Map<String, Object> rowM = new HashMap<String, Object>();
				for (int j = 0; j < colNum; j++) {
					rowM.put(getCellFormatValue(titlList.getCell(j)), getCellFormatValue(row.getCell(j))
							.trim());
				}
				list.add(rowM);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("导入Excel文件失败");
		}
		return list;
	}



	/**
	 * 读取Excel数据内容
	 * 标题匹配
	 * @param
	 * @return Map 包含单元格数据内容的Map对象
	 */
	public static List<Map<String, Object>> readExcelContent2(InputStream is) {
		List<Map<String, Object>> list = null;
		try {
			Workbook wb = WorkbookFactory.create(is);
			//wb = new XSSFWorkbook(is);

			sheet = wb.getSheetAt(0);
			// 得到总行数
			int rowNum = sheet.getLastRowNum();
			// 总列数
			row = sheet.getRow(1);
			Row titlList = sheet.getRow(1);
			int colNum = row.getLastCellNum();
			// 避免list多次扩容
			// list = new LinkedList<Map<String,Object>>();
			list = new ArrayList<Map<String, Object>>((int) (rowNum / 0.75));
			// 正文内容应该从第二行开始,第一行为表头的标题
			for (int i = 2; i <= rowNum; i++) {
				// 整行为空，continue
				row = sheet.getRow(i);
				if (row == null)
					continue;
				// 避免HashMap多次扩容
				Map<String, Object> rowM = new HashMap<String, Object>();
				for (int j = 0; j < colNum; j++) {
					rowM.put(getCellFormatValue(titlList.getCell(j)), getCellFormatValue(row.getCell(j))
							.trim());
				}
				list.add(rowM);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("导入Excel文件失败");
		}
		return list;
	}
	
	/**
	 * 根据HSSFCell类型设置数据
	 * 
	 * @param cell
	 * @return
	 */
	public static String getCellFormatValue(Cell cell) {
		String cellvalue = "";
//		if (cell != null) {
//			// 判断当前Cell的Type
//			switch (cell.getCellType()) {
//			// 如果当前Cell的Type为NUMERIC
////			case HSSFCell.CELL_TYPE_NUMERIC:
////			case HSSFCell.CELL_TYPE_FORMULA: {
//				// 判断当前的cell是否为Date
//				if (HSSFDateUtil.isCellDateFormatted(cell)) {
//					// 如果是Date类型则，转化为Data格式
//
//					// 方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
//					// cellvalue = cell.getDateCellValue().toLocaleString();
//
//					// 方法2：这样子的data格式是不带带时分秒的：2011-10-12
//					Date date = cell.getDateCellValue();
//					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//					cellvalue = sdf.format(date);
//
//				}
//				// 如果是纯数字
//				else {
//					// 取得当前Cell的数值
//					cellvalue = String.valueOf(cell.getNumericCellValue());
//					if (cellvalue.toUpperCase().indexOf("E") > -1
//							|| cellvalue.endsWith(".0") || cellvalue.endsWith(".00"))
//					{
//						BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
//						cellvalue = bd.toPlainString();
//					}
//				}
//				break;
//			}
//				// 如果当前Cell的Type为STRIN
////			case HSSFCell.CELL_TYPE_STRING:
////				// 取得当前的Cell字符串
////				cellvalue = cell.getRichStringCellValue().getString();
////				break;
////			// 默认的Cell值
////			default:
////				cellvalue = " ";
//			}
//		} else {
//			cellvalue = "";
//		}
		return cellvalue;

	}

	/**
	 * 导出Excel
	 * @param sheetName sheet名称
	 * @param values 内容
	 * @param wb HSSFWorkbook对象
	 * @return
	 */
	public static HSSFWorkbook getHSSFWorkbookNew(String sheetName, String[][]values, HSSFWorkbook wb){
		String[] title = { "区域", "项目总数(个)", "在建项目", "在建项目", "在建项目", "在建项目", "在建项目", "在建项目","停工项目(个)","完工项目(个)" };
		String[] handClum = { "0,0,2,0","0,1,2,1", "0,2,0,7","0,8,2,8","0,9,2,9" };
		String[] title2 = { "区域", "项目总数(个)", "项目基本情况及专户信息", "项目基本情况及专户信息", "项目基本情况及专户信息", "工资支付信息", "工资支付信息", "工资支付信息","停工项目(个)","完工项目(个)" };
		String[] handClum2 = {"1,2,1,4","1,5,1,7"};
		String[] title3 = {"区域","项目总数(个)", "应填项目数(个)", "已填项目数(个)", "未填项目数(个)", "应填项目数(个)", "已填项目数(个)", "未填项目数(个)", "停工项目(个)", "完工项目(个)"};
		// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
		if(wb == null){
			wb = new HSSFWorkbook();
		}
		// 在wb中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = wb.createSheet(sheetName);

		CellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);// 设置单元格水平居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);// 设置单元格垂直居中

		for (int i = 0; i < title.length; i++) {
			sheet.setColumnWidth(i, 5000);
		}

		// 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
		HSSFRow row = sheet.createRow(0);// 创建表头1
		for (int i = 0; i < title.length; i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellStyle(style);
			cell.setCellValue(title[i]);
		}
		// 动态合并单元格
		for (int i = 0; i < handClum.length; i++) {
			// sheet.autoSizeColumn(i, true);
			String[] temp = handClum[i].split(",");
			Integer startrow = Integer.parseInt(temp[0]);
			Integer overrow = Integer.parseInt(temp[2]);
			Integer startcol = Integer.parseInt(temp[1]);
			Integer overcol = Integer.parseInt(temp[3]);
			sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));
		}
		// 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
		HSSFRow row1 = sheet.createRow(1);// 创建表头2
		for (int i = 0; i < title2.length; i++) {
			HSSFCell cell = row1.createCell(i);
			cell.setCellStyle(style);
			cell.setCellValue(title2[i]);
		}
		// 动态合并单元格
		for (int i = 0; i < handClum2.length; i++) {
			// sheet.autoSizeColumn(i, true);
			String[] temp = handClum2[i].split(",");
			Integer startrow = Integer.parseInt(temp[0]);
			Integer overrow = Integer.parseInt(temp[2]);
			Integer startcol = Integer.parseInt(temp[1]);
			Integer overcol = Integer.parseInt(temp[3]);
			sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));
		}
		// 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
		HSSFRow row2 = sheet.createRow(2);// 创建表头2
		for (int i = 0; i < title3.length; i++) {
			HSSFCell cell = row2.createCell(i);
			cell.setCellStyle(style);
			cell.setCellValue(title3[i]);
		}
		//创建内容
		for(int i=0;i<values.length;i++){
			row = sheet.createRow(i + 3);
			for(int j=0;j<values[i].length;j++){
				//将内容按顺序赋给对应的列对象
				row.createCell(j).setCellValue(values[i][j]);
			}
		}
		return wb;
	}

	/**
	 * 导出Excel
	 * @param sheetName sheet名称
	 * @param title 标题
	 * @param values 内容
	 * @param wb HSSFWorkbook对象
	 * @return
	 */
	public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[]title, String[][]values, HSSFWorkbook wb){

		// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
		if(wb == null){
			wb = new HSSFWorkbook();
		}

		// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = wb.createSheet(sheetName);
		int[] width={2,3,6,6,6,4,5,5,4,4,5,4,4,3,4};
		for (int i = 0; i < title.length; i++) {
			//sheet.trackAllColumnsForAutoSizing();
			//sheet.autoSizeColumn(i);
			sheet.setColumnWidth(i, width[i]*1000);
		}
		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
		HSSFRow row = sheet.createRow(0);

		// 第四步，创建单元格，并设置值表头 设置表头居中
		HSSFCellStyle style = wb.createCellStyle();
//		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

		//声明列对象
		HSSFCell cell = null;

		//创建标题
		for(int i=0;i<title.length;i++){
			cell = row.createCell(i);
			cell.setCellValue(title[i]);
			cell.setCellStyle(style);
		}

		//创建内容
		for(int i=0;i<values.length;i++){
			row = sheet.createRow(i + 1);
			for(int j=0;j<values[i].length;j++){
				//将内容按顺序赋给对应的列对象
				row.createCell(j).setCellValue(values[i][j]);
			}
		}
		return wb;
	}

	public static void exportExcel(HttpServletResponse response, String filename, String[] heads, List<String[]> datas) throws Exception {
		//response.setHeader("Content-disposition", filename);
		response.setContentType("application/vnd.ms-excel;charset=utf-8"); //mime类型
		//response.setContentType("application/octet-stream;charset=ISO8859-1");
		response.setHeader("Content-Disposition", "attachment;filename="+filename);
		response.setHeader("Pragma", "No-cache");
		response.addHeader("Cache-Control", "no-cache");
		response.setCharacterEncoding("UTF-8");
		//创建一个新的工作薄
		SXSSFWorkbook wb = new SXSSFWorkbook();
		// 设置字体
		CellStyle redStyle = wb.createCellStyle();
		Font redFont = wb.createFont();
		//颜色
		redFont.setColor(Font.COLOR_RED);
		//设置字体大小
		//redFont.setFontHeightInPoints((short) 10);
		//字体
		//redFont.setFontName("宋体");
		redStyle.setFont(redFont);
		//创建一张表
		Sheet sh = wb.createSheet();
		int[] width={3,5,5,6,5,5,3,3,3,4,4,4,4};
		for (int i = 0; i < heads.length; i++) {
			//sheet.trackAllColumnsForAutoSizing();
			//sheet.autoSizeColumn(i);
			sh.setColumnWidth(i, width[i]*1000);
		}
		//一.创建表中的头部数据
		// ①.创建头这一行
		Row headRow = sh.createRow(0);
		// ②.遍历heads (每个值就是一个列)
		for (int i = 0; i < heads.length; i++) {
			//③.创建头部对应的列
			Cell cell = headRow.createCell(i);
			/*if(i==heads.length-1){
					cell.setCellStyle(redStyle);
				}*/
			//④.把数据放到列中
			cell.setCellValue(heads[i]);
		}
		//二.创建表中的数据
		// ①.遍历datas(每一次遍历都是一个新的行)
		for (int i = 0; i < datas.size(); i++) {
			//②.创建一个数据行
			Row row = sh.createRow(i+1);
			//③.拿到这个行的所有数据，遍历它，再创建列
			String[] rowDatas = datas.get(i);
			for (int j = 0; j < rowDatas.length; j++) {
				//④.创建对应的列(这一行的格子)
				Cell cell = row.createCell(j);
				//⑤.把数据放到这个格子中
				cell.setCellValue(rowDatas[j]);
			}
		}
		ServletOutputStream out = response.getOutputStream();
		wb.write(out);
		out.close();
	}

	public static void exportExcelLock(HttpServletResponse response, String filename, String[] heads, List<String[]> datas, Map<String, Object> map, Map<String, Object> payinfoMap) throws Exception {
		//response.setHeader("Content-disposition", filename);
		if(payinfoMap.get("data").toString().equals("2")){
			response.setContentType("application/vnd.ms-excel;charset=utf-8");
		}else{
			response.setContentType("text/plain;charset=utf-8"); //mime类型
		}
		response.setHeader("Content-Disposition", "attachment;filename="+filename);
		response.setHeader("Pragma", "No-cache");
		response.addHeader("Cache-Control", "no-cache");
		response.setCharacterEncoding("UTF-8");
		//创建一个新的工作薄
		//Workbook   wb = new HSSFWorkbook();
		SXSSFWorkbook wb = new SXSSFWorkbook();
		//创建一张表
		Sheet sheet = wb.createSheet();
		int[] width={2,9,10,6,15,2,2,3,6,6,7,6,3,16};
		for (int i = 0; i < heads.length; i++) {
			//sheet.trackAllColumnsForAutoSizing();
			//sheet.autoSizeColumn(i);
			sheet.setColumnWidth(i, width[i]*1000);
		}
		sheet.protectSheet("");
		//一.创建表中的头部数据
		// ①.创建头这一行
		Row headRow = sheet.createRow(0);
		//单元格锁定的样式
		CellStyle style = wb.createCellStyle();
		style.setLocked(false);
		mergeCell(sheet,1,1,4,7);
		// ②.遍历heads (每个值就是一个列)
		for (int i = 0; i < heads.length; i++) {
			//③.创建头部对应的列
			Cell cell = headRow.createCell(i);
			//④.把数据放到列中
			cell.setCellValue(heads[i]);
			cell.setCellStyle(style);
		}
		//二.创建表中的数据
		// ①.遍历datas(每一次遍历都是一个新的行)
		for (int i = 0; i < datas.size(); i++) {
			//②.创建一个数据行
			Row row =sheet.createRow(i+1);
			//③.拿到这个行的所有数据，遍历它，再创建列
			String[] rowDatas = datas.get(i);
			for (int j = 0; j < rowDatas.length; j++) {
				//④.创建对应的列(这一行的格子)
				Cell cell = row.createCell(j);
				//⑤.把数据放到这个格子中
				cell.setCellValue(rowDatas[j]);
				cell.setCellStyle(style);
			}
		}
		if(payinfoMap.get("data").toString().equals("1")){
			BufferedOutputStream buff = null;
			ServletOutputStream outStr = null;
			try {
				//临时缓冲区
				ByteArrayOutputStream byteout = new ByteArrayOutputStream();
				//创建临时文件
				wb.write(byteout);
				byte [] bookByteAry = byteout.toByteArray();
				String base64String = Base64.encodeBase64String(bookByteAry);
				base64String = base64String.replaceAll("\n", "").replaceAll("\r", "");//删除 \r\n
//				int number = RandomsUtil.number(base64String.length());
				String privateCode =(String) map.get("privateCode");
				StringBuffer sb = new StringBuffer();
				//二次补报
				String num="001";
				if(payinfoMap.get("type").toString().equals("1")){
					num="002";
				}
				sb.append(payinfoMap.get("monthId").toString()+num);
//				sb.append(base64String.substring(0, number));
//				sb.append(privateCode);
//				sb.append(base64String.substring(number));
				outStr = response.getOutputStream();
				buff = new BufferedOutputStream(outStr);
				buff.write(sb.toString().getBytes("UTF-8"));
				buff.flush();
				buff.close();
			} catch (Exception e) {
              e.printStackTrace();
			} finally {try {
				buff.close();
				outStr.close();
			} catch (Exception e) {
			}
			}
		}else{
			ServletOutputStream out = response.getOutputStream();
			wb.write(out);
			out.close();
		}
	}
	public static void exporttext(HttpServletResponse response, String filename, Workbook workbook) throws Exception {
		response.setContentType("application/vnd.ms-excel;charset=utf-8"); //mime类型
		response.setHeader("Content-Disposition", "attachment;filename="+filename);
		response.setHeader("Pragma", "No-cache");
		response.addHeader("Cache-Control", "no-cache");
		response.setCharacterEncoding("UTF-8");
		try {
			ServletOutputStream outputStream = response.getOutputStream();
			workbook.write(outputStream);
			outputStream.close();
		}catch (Exception e){
			e.printStackTrace();
		}
	}
	/**
	 * @param inputStream ：传入一个输入流(前台传过来的)
	 *             List<String[]> -> 读取到的数据
	 */
	public static List<String[]> importExcel(InputStream inputStream) throws Exception {
		//①.准备返回的数据
		List<String[]> datas = new ArrayList<>();
		//把Excel文件读取到内存中(我们的Java代码就可以获取了)
		Workbook wb = WorkbookFactory.create(inputStream);
		//拿到第一张表
		Sheet sheet = wb.getSheetAt(0);
		//拿到表的总行数(最后一行,从0开始算)
		int lastRowNum = sheet.getLastRowNum();
		for (int i = 1; i <= lastRowNum; i++) {
			//拿到某一行
			Row row = sheet.getRow(i);
			//拿到这一行的格子数量（这一行有多少列）
			short lastCellNum = row.getLastCellNum();
			//②.创建String[]数组 -> 每一个String数组都代表是一行数据
			String[] rowData = new String[lastCellNum];
			for (int j = 0; j < lastCellNum; j++) {
				//拿到这一列的某个格子数据
				Cell cell = row.getCell(j);
				//③.把数据装到数组中
				rowData[j] = cell.getStringCellValue();
			}
			//④.把这一行数据放到List中
			datas.add(rowData);
		}
		return  datas;
	}

	/**
	 * 导出Excel
	 * @return
	 */

	public static void exportToExcel(HttpServletRequest request, HttpServletResponse response, @RequestParam(required=false) Map<String, Object> params) throws Exception {
		if(params==null) {
			params=new HashMap<>();
		}
		String in=request.getSession().getServletContext().getRealPath("");
		System.out.println(in);
		//制作Excel并导出
		List<Map<String, Object>> filedAndZh=(List<Map<String, Object>>) params.get("titleList");
		List<Map<String, Object>> list=(List<Map<String, Object>>) params.get("list");
		List<Short> cellWidth=(List<Short>) params.get("cellWidth");
		String sheetName="用户查询结果";
		FileOutputStream fos=null;
		fos = new FileOutputStream(in+"jjjj.xlsx");
		HSSFWorkbook workBook = new HSSFWorkbook();
		HSSFSheet sheet = workBook.createSheet();
		workBook.setSheetName(0,sheetName);
		HSSFHeader header = sheet.getHeader();
		header.setCenter(sheetName);
		HSSFRow row = sheet.createRow(0);
		int fAZLength= filedAndZh.get(0).size();
		//设置“序号”列
		HSSFCell cell = row.createCell(0);
		sheet.setColumnWidth((short) 0, (short)2000);
		cell.setCellValue("序号");
		try {
			for (int i = 0; i < fAZLength; i++) {
				cell = row.createCell(i+1);
				//设置列宽
				short sName= (short)(i+1);
				Object obj = cellWidth.get(i);
				String str1 = obj.toString();
				Short sValue=(short)(Integer.parseInt(str1)*100);

				sheet.setColumnWidth(sName,sValue);
				//获取entrySet的value值
				String str=filedAndZh.get(0).entrySet().toArray()[i]+"";
				String ss=str.substring(str.lastIndexOf("=")+1);

				cell.setCellValue(ss);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if(list!=null){
			int index=list.size();
			String reg="^(-)?\\d+(\\.\\d+)?$";
			for (int i = 0; i < index; i++) {
				HSSFRow rowshow = sheet.createRow(i+1);
				Map map  = list.get(i);
				//设置序号
				cell = rowshow.createCell(0);
				cell.setCellValue(i+1);
				//循环添加数据
				for (int j = 0; j < filedAndZh.get(0).size(); j++) {
					cell = rowshow.createCell(j+1);
					//获取entrySet的key值
					String str=filedAndZh.get(0).entrySet().toArray()[j]+"";
					String ss=str.substring(0,str.lastIndexOf("="));
					//空值处理
					if(null==map.get(ss)||"null".equals(map.get(ss))){
						cell.setCellValue("");
					}else{
//								if((map.get(ss)+"").matches(reg)&&(map.get(ss)+"").length()<12){
//									cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
//									cell.setCellValue(Double.parseDouble(map.get(ss)+""));
//									HSSFCellStyle cellStyle = workBook.createCellStyle();
//									if(map.get(ss).toString().contains(".")){
//								    cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
//									}else{
//										cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
//									}
//								    cell.setCellStyle(cellStyle);
//								}else{
						cell.setCellValue(map.get(ss)+"");
//								}
					}
				}
			}
		}
		try {
			response.addHeader("Content-Disposition", "attachment;filename=\""
					+ new String((sheetName).getBytes("utf-8"),
					"ISO8859-1") + "\"");
			response.setStatus(HttpStatus.OK.value());
			response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
			response.setHeader("Cache-Control", "no-cache, must-revalidate");
			response.setHeader("Access-Control-Allow-Origin", request.getHeader("Origin"));
			response.setHeader("Access-Control-Allow-Credentials", "true");
			response.setHeader("Access-Control-Allow-Methods", "POST, GET, OPTIONS, DELETE");
			response.setHeader("Access-Control-Max-Age", "3600");
			response.setHeader("Access-Control-Allow-Headers", "x-requested-with");
			OutputStream out = response.getOutputStream();
			workBook.write(fos);
			HttpSession session=request.getSession();
			session.setAttribute("path",in+"jjjj.xlsx");
			out.flush();
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	
	/**
	 * 导出统计查询Excel（非通用）
	 * @return
	 */

	public static void exportToExcelWithName(HttpServletRequest request, HttpServletResponse response, @RequestParam Map<String, Object> params) throws Exception {
//		if(params==null) {
//			params=new HashMap<>();
//		}
		//制作Excel并导出
		List<Map<String, Object>> filedAndZh=(List<Map<String, Object>>) params.get("titleList");
		List<Map<String, Object>> list=(List<Map<String, Object>>) params.get("list");
		List<Short> cellWidth=(List<Short>) params.get("cellWidth");
		String sheetName="用户查询结果";
//		String 	sheetName1=params.get("name").toString();
		HSSFWorkbook workBook = new HSSFWorkbook();
		HSSFSheet sheet = workBook.createSheet();
		workBook.setSheetName(0,sheetName);
		HSSFHeader header = sheet.getHeader();
		header.setCenter(sheetName);
		HSSFRow row2 = sheet.createRow(1);
		HSSFRow row1 = sheet.createRow(0);
		int fAZLength= filedAndZh.get(0).size();
		//设置“序号”列
		HSSFCell cell = row1.createCell(0);
		sheet.setColumnWidth((short) 0, (short)2000);
		cell.setCellValue("序号");
		//设置表头
		HSSFCell cell13 = row1.createCell(3);
		sheet.setColumnWidth((short) 0, (short)2000);
		cell13.setCellValue("施工总承包企业填报数据");
		HSSFCell cell17 = row1.createCell(7);
		sheet.setColumnWidth((short) 0, (short)2000);
		cell17.setCellValue("银行反馈数据");
		HSSFCell cell110 = row1.createCell(10);
		sheet.setColumnWidth((short) 0, (short)2000);
		cell110.setCellValue("施工总承包企业补录数据");
		HSSFCell cell112 = row1.createCell(14);
		sheet.setColumnWidth((short) 0, (short)2000);
		cell112.setCellValue("银行补录数据");
		
		mergeCell(sheet, 0, 0, 3, 6);
		mergeCell(sheet, 0, 0, 7, 9);
		mergeCell(sheet, 0, 0, 10, 13);
		mergeCell(sheet, 0, 0, 14, 16);
		try {
			for (int i = 0; i < fAZLength; i++) {
				cell = row2.createCell(i+1);
				if(i<2||i>15) {
					cell = row1.createCell(i+1);
				}
				
				//设置列宽
				short sName= (short)(i+1);
				Object obj = cellWidth.get(i);
				String str1 = obj.toString();
				Short sValue=(short)(Integer.parseInt(str1));

				sheet.setColumnWidth(sName,sValue);
				//获取entrySet的value值
				String str=filedAndZh.get(0).entrySet().toArray()[i]+"";
				String ss=str.substring(str.lastIndexOf("=")+1);

				cell.setCellValue(ss);
			}
			
//			mergeCell(sheet, 0, 1, 0, 0);
			mergeCell(sheet, 0, 1, 0, 0);
			mergeCell(sheet, 0, 1, 1, 1);
			mergeCell(sheet, 0, 1, 2, 2);
			mergeCell(sheet, 0, 1, 17, 17);
			mergeCell(sheet, 0, 1, 18, 18);
			mergeCell(sheet, 0, 1, 19, 19);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if(list!=null){
			int index=list.size();
			for (int i = 0; i < index; i++) {
				HSSFRow rowshow = sheet.createRow(i+2);
				Map map  = list.get(i);
				//设置序号
				cell = rowshow.createCell(0);
				cell.setCellValue(i+1);
				//循环添加数据
				for (int j = 0; j < filedAndZh.get(0).size(); j++) {
					cell = rowshow.createCell(j+1);
					//获取entrySet的key值
					String str=filedAndZh.get(0).entrySet().toArray()[j]+"";
					String ss=str.substring(0,str.lastIndexOf("="));
					//空值处理
					if(null==map.get(ss)||"null".equals(map.get(ss))){
						cell.setCellValue("");
					}else{
						cell.setCellValue(map.get(ss)+"");
					}
				}
			}
		}
		try {
			response.addHeader("Content-Disposition", "attachment;filename=\""
					+ new String((sheetName).getBytes("utf-8"),
					"ISO8859-1") + "\"");
			response.setStatus(HttpStatus.OK.value());
			response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
			response.setHeader("Cache-Control", "no-cache, must-revalidate");
			response.setHeader("Access-Control-Allow-Origin", request.getHeader("Origin"));
			response.setHeader("Access-Control-Allow-Credentials", "true");
			response.setHeader("Access-Control-Allow-Methods", "POST, GET, OPTIONS, DELETE");
			response.setHeader("Access-Control-Max-Age", "3600");
			response.setHeader("Access-Control-Allow-Headers", "x-requested-with");
			
			ServletOutputStream outputStream = response.getOutputStream();
			workBook.write(outputStream);
			outputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 导出保障金统计查询Excel（非通用）
	 * @return
	 */

	public static void exportToExcelForSecurity(HttpServletRequest request, HttpServletResponse response, @RequestParam Map<String, Object> params) throws Exception {
//		if(params==null) {
//			params=new HashMap<>();
//		}
		//制作Excel并导出
		List<Map<String, Object>> filedAndZh=(List<Map<String, Object>>) params.get("titleList");
		List<Map<String, Object>> list=(List<Map<String, Object>>) params.get("list");
		List<Short> cellWidth=(List<Short>) params.get("cellWidth");
		String sheetName="保障金统查询结果";
//		String 	sheetName1=params.get("name").toString();
		HSSFWorkbook workBook = new HSSFWorkbook();
		HSSFSheet sheet = workBook.createSheet();
		workBook.setSheetName(0,sheetName);
		HSSFHeader header = sheet.getHeader();
		header.setCenter(sheetName);
		HSSFRow row2 = sheet.createRow(1);
		HSSFRow row1 = sheet.createRow(0);
		int fAZLength= filedAndZh.get(0).size();
		//设置“序号”列
		HSSFCell cell = row1.createCell(0);
		sheet.setColumnWidth((short) 0, (short)2000);
		cell.setCellValue("序号");
		//设置表头
		HSSFCell cell13 = row1.createCell(3);
		sheet.setColumnWidth((short) 0, (short)2000);
		cell13.setCellValue("现金收取（集中账户）");
		HSSFCell cell17 = row1.createCell(6);
		sheet.setColumnWidth((short) 0, (short)2000);
		cell17.setCellValue("银行保函");
		
		mergeCell(sheet, 0, 0, 3, 5);
		mergeCell(sheet, 0, 0, 6, 9);
		try {
			for (int i = 0; i < fAZLength; i++) {
				cell = row2.createCell(i+1);
				if(i<2||i>8) {
					cell = row1.createCell(i+1);
				}
				
				//设置列宽
				short sName= (short)(i+1);
				Object obj = cellWidth.get(i);
				String str1 = obj.toString();
				Short sValue=(short)(Integer.parseInt(str1));

				sheet.setColumnWidth(sName,sValue);
				//获取entrySet的value值
				String str=filedAndZh.get(0).entrySet().toArray()[i]+"";
				String ss=str.substring(str.lastIndexOf("=")+1);

				cell.setCellValue(ss);
			}
			
			mergeCell(sheet, 0, 1, 0, 0);
			mergeCell(sheet, 0, 1, 1, 1);
			mergeCell(sheet, 0, 1, 2, 2);

		} catch (Exception e) {
			e.printStackTrace();
		}
		if(list!=null){
			int index=list.size();
			for (int i = 0; i < index; i++) {
				HSSFRow rowshow = sheet.createRow(i+2);
				Map map  = list.get(i);
				//设置序号
				cell = rowshow.createCell(0);
				cell.setCellValue(i+1);
				//循环添加数据
				for (int j = 0; j < filedAndZh.get(0).size(); j++) {
					cell = rowshow.createCell(j+1);
					//获取entrySet的key值
					String str=filedAndZh.get(0).entrySet().toArray()[j]+"";
					String ss=str.substring(0,str.lastIndexOf("="));
					//空值处理
					if(null==map.get(ss)||"null".equals(map.get(ss))){
						cell.setCellValue("");
					}else{
						cell.setCellValue(map.get(ss)+"");
					}
				}
			}
		}
		try {
			response.addHeader("Content-Disposition", "attachment;filename=\""
					+ new String((sheetName).getBytes("utf-8"),
					"ISO8859-1") + "\"");
			response.setStatus(HttpStatus.OK.value());
			response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
			response.setHeader("Cache-Control", "no-cache, must-revalidate");
			response.setHeader("Access-Control-Allow-Origin", request.getHeader("Origin"));
			response.setHeader("Access-Control-Allow-Credentials", "true");
			response.setHeader("Access-Control-Allow-Methods", "POST, GET, OPTIONS, DELETE");
			response.setHeader("Access-Control-Max-Age", "3600");
			response.setHeader("Access-Control-Allow-Headers", "x-requested-with");
			
			ServletOutputStream outputStream = response.getOutputStream();
			workBook.write(outputStream);
			outputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
